From 57eed0dd3f16fbc53c79f8989f5d96df50001a50 Mon Sep 17 00:00:00 2001 From: Stevan Earl Date: Sun, 9 Aug 2026 18:13:18 -0700 Subject: [PATCH] feat: add B-record groom-scan conversion loader Split from grm_scan commit 36b5c5d. The GS_comments NULL-to-empty conversion is performed in clean.sql. --- conversion/load_groom_scans_b.sql | 183 ++++++++++++++++++++++++++++++ 1 file changed, 183 insertions(+) create mode 100644 conversion/load_groom_scans_b.sql diff --git a/conversion/load_groom_scans_b.sql b/conversion/load_groom_scans_b.sql new file mode 100644 index 0000000..3921ad5 --- /dev/null +++ b/conversion/load_groom_scans_b.sql @@ -0,0 +1,183 @@ +-- Copyright (C) 2026 The Meme Factory, Inc. http://www.karlpinc.com/ +-- +-- This program is free software: you can redistribute it and/or modify +-- it under the terms of the GNU Affero General Public License as +-- published by the Free Software Foundation, either version 3 of the +-- License, or (at your option) any later version. +-- +-- This program is distributed in the hope that it will be useful, +-- but WITHOUT ANY WARRANTY; without even the implied warranty of +-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +-- GNU Affero General Public License for more details. +-- +-- You should have received a copy of the GNU Affero General Public License +-- along with this program. If not, see . +-- +-- Karl O. Pinc + +-- Setup the search path +SET search_path TO sokwedb, codes, lib, clean, libconv; + + +-- groom_scans -> watches, events, roles, groom_scans_b + +DO $$ +DECLARE + this_gs clean.groom_scans%ROWTYPE; + related_wid watches.wid%TYPE; + related_commid watches.commid%TYPE; + event_eid events.eid%TYPE; + others_value groom_scans_b.others%TYPE; + duplicate_value groom_scans_b.duplicate%TYPE; + +BEGIN + FOR this_gs IN + SELECT * + FROM clean.groom_scans + ORDER BY groom_scans.gs_date + , groom_scans.gs_fol_b_focal_animid + , groom_scans.gs_time + , groom_scans.gs_b_chimp1_animid + , groom_scans.gs_b_chimp2_animid + LOOP + -- Which watch belongs to the groom scan? + SELECT watches.wid + INTO related_wid + FROM watches + WHERE watches.animid = this_gs.gs_fol_b_focal_animid + AND watches.date = this_gs.gs_date + AND watches.type = 'B'; + + IF NOT FOUND THEN + -- The groom scan is part of a B-record follow. Make the minimum + -- WATCHES row needed when follows have not already been converted. + SELECT follow.fol_cl_community_id + INTO STRICT related_commid + FROM clean.follow + WHERE follow.fol_b_animid = this_gs.gs_fol_b_focal_animid + AND follow.fol_date = this_gs.gs_date; + + INSERT INTO watches ( + animid + , commid + , date + , type + , notes) + VALUES ( + this_gs.gs_fol_b_focal_animid + , related_commid + , this_gs.gs_date + , 'B' + , '') + RETURNING wid INTO related_wid; + END IF; + + -- groom_scans -> events + INSERT INTO events ( + wid + , behavior + , start + , stop + , certainty + , notes) + VALUES ( + related_wid + , 'GSCAN' + , this_gs.gs_time + , this_gs.gs_time + , '1' + , this_gs.gs_comments) + RETURNING eid INTO event_eid; + + -- groom_scans -> roles + CASE this_gs.gs_direction + WHEN 'G' THEN + INSERT INTO roles (eid, role, participant) + VALUES (event_eid, 'Actor', this_gs.gs_b_chimp1_animid); + INSERT INTO roles (eid, role, participant) + VALUES (event_eid, 'Actee', this_gs.gs_b_chimp2_animid); + WHEN 'R' THEN + INSERT INTO roles (eid, role, participant) + VALUES (event_eid, 'Actee', this_gs.gs_b_chimp1_animid); + INSERT INTO roles (eid, role, participant) + VALUES (event_eid, 'Actor', this_gs.gs_b_chimp2_animid); + WHEN 'M' THEN + INSERT INTO roles (eid, role, participant) + VALUES (event_eid, 'Mutual', this_gs.gs_b_chimp1_animid); + INSERT INTO roles (eid, role, participant) + VALUES (event_eid, 'Mutual', this_gs.gs_b_chimp2_animid); + WHEN 'U' THEN + INSERT INTO roles (eid, role, participant) + VALUES (event_eid, 'UNKPair', this_gs.gs_b_chimp1_animid); + INSERT INTO roles (eid, role, participant) + VALUES (event_eid, 'UNKPair', this_gs.gs_b_chimp2_animid); + ELSE + RAISE data_exception USING + MESSAGE = 'Unknown GROOM_SCANS.GS_direction value', + DETAIL = 'Value (GS_direction) = (' + || COALESCE(this_gs.gs_direction, 'NULL') + || '), Value (GS_date) = (' + || this_gs.gs_date + || '), Value (GS_FOL_B_focal_AnimID) = (' + || this_gs.gs_fol_b_focal_animid + || '), Value (GS_time) = (' + || COALESCE(this_gs.gs_time::TEXT, 'NULL') + || ')'; + END CASE; + + -- Convert source flags to booleans. + CASE this_gs.gs_other_partners_flag + WHEN 'Y' THEN others_value := TRUE; + WHEN 'N' THEN others_value := FALSE; + ELSE + RAISE data_exception USING + MESSAGE = 'Unknown GROOM_SCANS.GS_other_partners_flag value', + DETAIL = 'Value (GS_other_partners_flag) = (' + || COALESCE(this_gs.gs_other_partners_flag, 'NULL') + || '), Value (GS_date) = (' + || this_gs.gs_date + || '), Value (GS_FOL_B_focal_AnimID) = (' + || this_gs.gs_fol_b_focal_animid + || '), Value (GS_time) = (' + || COALESCE(this_gs.gs_time::TEXT, 'NULL') + || ')'; + END CASE; + + CASE + WHEN this_gs.gs_duplicate_flag = 'X' THEN + duplicate_value := TRUE; + WHEN this_gs.gs_duplicate_flag IS NULL THEN + duplicate_value := FALSE; + ELSE + RAISE data_exception USING + MESSAGE = 'Unknown GROOM_SCANS.GS_duplicate_flag value', + DETAIL = 'Value (GS_duplicate_flag) = (' + || this_gs.gs_duplicate_flag + || '), Value (GS_date) = (' + || this_gs.gs_date + || '), Value (GS_FOL_B_focal_AnimID) = (' + || this_gs.gs_fol_b_focal_animid + || '), Value (GS_time) = (' + || COALESCE(this_gs.gs_time::TEXT, 'NULL') + || ')'; + END CASE; + + -- groom_scans -> groom_scans_b + INSERT INTO groom_scans_b ( + eid + , others + , duplicate + , extractedby) + VALUES ( + event_eid + , others_value + , duplicate_value + , this_gs.gs_extracted_by); + END LOOP; +END; +$$; + +ANALYZE watches; +ANALYZE events; +ANALYZE roles; +ANALYZE groom_scans_b; -- 2.34.1